package com.test.datebase;

import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.junit.Test;

import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * @author 李玄玄
 * @date 2024年07月13日 17:31
 */
public class Mysql {

    @Test
    public void export() {
        // 定义连接数据库所需的 URL、用户名和密码
        String url = "jdbc:mysql://120.24.64.31:3307/zhonghe_vue_pro?useSSL=false&serverTimezone=Asia/Shanghai";
        String user = "cdxm_dev";
        String password = "^jumoon_dev31";

        //  加载 MySQL JDBC 驱动程序
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }

        // 使用 JDBC 连接到数据库
        try (Connection connection = DriverManager.getConnection(url, user, password);
             Statement stmt = connection.createStatement()) {

            // 获取数据库元数据信息
            DatabaseMetaData metaData = connection.getMetaData();
            String catalog = connection.getCatalog();

            // 存储表名和表描述
            List<String> tableNames = new ArrayList<>();
            List<String> tableRemarks = new ArrayList<>();

            // 获取数据库中的所有表
            ResultSet tables = metaData.getTables(catalog, null, null, new String[]{"TABLE"});

            List<String> fitlter = new ArrayList<>();
            fitlter.add("act_");
            fitlter.add("bpm_");
            fitlter.add("pay_");
            fitlter.add("infra_");
            fitlter.add("qrtz_");
            fitlter.add("system_");
            fitlter.add("flw_");


            // 遍历 tables 结果集，获取表名和表描述
            while (tables.next()) {
                String tableName = tables.getString("TABLE_NAME");
                String tableRemark = tables.getString("REMARKS");

                boolean isFilter = false;
                for (String filter : fitlter) {
                    if (tableName.toLowerCase().contains(filter)) {
                        isFilter = true;
                        break;
                    }
                }
                if (!isFilter) {
                    if (StringUtils.isBlank(tableRemark)) {
                        String sql = "SHOW TABLE STATUS WHERE Name = '" + tableName + "'";

                        try (ResultSet rs = stmt.executeQuery(sql)) {
                            if (rs.next()) {
                                tableRemark = rs.getString("Comment");
                            }
                        }
                    }

                    tableNames.add(tableName);
                    tableRemarks.add(tableRemark);
                }
            }

            // 创建新的 Excel 文件和一个 "xxxx" 的工作表
            XSSFWorkbook workbook = new XSSFWorkbook();
            XSSFSheet sheet = workbook.createSheet("科技管理平台");

            int rowNum = 0;
            int i = 0;
            for (String tableName : tableNames) {
                // 每个表结构的表头
                Row headerRow = sheet.createRow(rowNum++);
                headerRow.createCell(0).setCellValue("表名");
                headerRow.createCell(1).setCellValue("表描述");
                headerRow.createCell(2).setCellValue("列名");
                headerRow.createCell(3).setCellValue("数据类型");
                headerRow.createCell(4).setCellValue("非空");
                headerRow.createCell(5).setCellValue("自增");
                headerRow.createCell(6).setCellValue("默认");
                headerRow.createCell(7).setCellValue("注释");
                Row tableRow = sheet.createRow(rowNum++);
                tableRow.createCell(0).setCellValue(tableName);
                tableRow.createCell(1).setCellValue(tableRemarks.get(i++));

                // 该表的所有字段结构
                ResultSet columns = metaData.getColumns(catalog, null, tableName, null);

                while (columns.next()) {
                    String columnName = columns.getString("COLUMN_NAME");
                    String columnType = columns.getString("TYPE_NAME");
                    String columnSize = columns.getString("COLUMN_SIZE");
                    boolean isNullable = (columns.getInt("NULLABLE") == 0);
                    boolean isAutoIncrement = columns.getString("IS_AUTOINCREMENT").equals("YES");
                    String defaultValue = columns.getString("COLUMN_DEF");
                    String remarks = columns.getString("REMARKS");
                    Row columnRow = sheet.createRow(rowNum++);
                    columnRow.createCell(2).setCellValue(columnName);
                    columnRow.createCell(3).setCellValue(columnType + "(" + columnSize + ")");
                    columnRow.createCell(4).setCellValue(isNullable);
                    columnRow.createCell(5).setCellValue(isAutoIncrement);
                    columnRow.createCell(6).setCellValue(defaultValue);
                    columnRow.createCell(7).setCellValue(remarks);
                }
                columns.close();
                sheet.createRow(rowNum++);
                sheet.createRow(rowNum++);
                sheet.createRow(rowNum++);
            }

            // 写入Excel文件
            try (FileOutputStream outputStream = new FileOutputStream("D:\\tiups.xlsx")) {
                workbook.write(outputStream);
            } catch (IOException e) {
                throw new RuntimeException(e);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }

    }
}
